/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.apps.controllers;

import com.apps.beans.AccountBean;
import com.apps.beans.EnvironmentBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 *
 * @author ARIJIT PAL
 */
public class AccountSetupManager {

    public static void setup() throws Exception {

        String createEnvTableSQL = "CREATE TABLE IF NOT EXISTS \"LogEXEnv\" ( "
                + "\"code\" INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "\"name\" TEXT,"
                + "\"path\" TEXT,"
                + "\"scriptpath\" TEXT)";
        String createAccTableSQL = "CREATE TABLE IF NOT EXISTS \"LogEXAcc\" ( "
                + "\"code\" INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "\"envcode\" INTEGER,"
                + "\"host\" TEXT,"
                + "\"port\" TEXT,"
                + "\"username\" TEXT,"
                + "\"password\" TEXT,"
                + "\"path\" TEXT,"
                + "\"scriptpath\" TEXT )";

        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();
        stat.executeUpdate(createEnvTableSQL);
        stat.executeUpdate(createAccTableSQL);
        stat.close();
        conn.close();*/
        DBController.updateData(createEnvTableSQL);
        DBController.updateData(createAccTableSQL);
    }

    public static ArrayList<EnvironmentBean> getEnvList() throws Exception{
        setup();
        ArrayList<EnvironmentBean> list = new ArrayList<EnvironmentBean>();
        String envListSql = "select * from LogEXEnv";
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(envListSql);*/
        List<Map<String, Object>> rs = DBController.getResult(envListSql);
        //while(rs.next()){
        for(Map<String, Object> data : rs){
            EnvironmentBean bean = new EnvironmentBean();
            bean.setCode((String)data.get("code"));
            bean.setName((String)data.get("name"));
            bean.setPath((String)data.get("path"));
            bean.setScriptPath((String)data.get("scriptpath"));
            list.add(bean);
        }
        return list;
    }

    public static EnvironmentBean getEnv(String envCode) throws Exception{
        setup();
        EnvironmentBean bean = new EnvironmentBean();
        String envListSql = "select * from LogEXEnv where code="+envCode+" ";
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(envListSql);*/
        List<Map<String, Object>> rs = DBController.getResult(envListSql);
        //while(rs.next()){
        //for(Map<String, Object> data : rs){
        if(rs.size()>0){
            Map<String, Object> data = rs.get(0);
            bean.setCode((String)data.get("code"));
            bean.setName((String)data.get("name"));
            bean.setPath((String)data.get("path"));
            bean.setScriptPath((String)data.get("scriptpath"));
            //list.add(bean);
        }
        return bean;
    }

    public static ArrayList<AccountBean> getAccList(String envCode) throws Exception{
        ArrayList<AccountBean> list = new ArrayList<AccountBean>();
        String accListSql = "select * from LogEXAcc where envcode = '"+envCode+"' ";
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(accListSql);*/
        List<Map<String, Object>> rs = DBController.getResult(accListSql);
        //while(rs.next()){
        for(Map<String, Object> data : rs){
            AccountBean bean = new AccountBean();
            bean.setCode((String)data.get("code"));
            bean.setEnvCode((String)data.get("envcode"));
            bean.setHost((String)data.get("host"));
            bean.setPort((String)data.get("port"));
            bean.setUserName((String)data.get("username"));
            bean.setPassword((String)data.get("password"));
            bean.setPath((String)data.get("path"));
            bean.setScriptPath((String)data.get("scriptpath"));
            list.add(bean);
        }
        return list;
    }

    public static boolean updateEnv(EnvironmentBean bean) throws Exception {
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();*/
        String sql = "update LogEXEnv set name = '" + bean.getName()
                + "', path = '" + bean.getPath() + "', scriptpath = '"
                + bean.getScriptPath() + "' where code=" + bean.getCode() + " ";
        /*stat.executeUpdate(sql);
        stat.close();
        conn.close();*/
        DBController.updateData(sql);
        return true;
    }
    public static boolean updateAcc(AccountBean bean) throws Exception{
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();*/
        String sql = "update LogEXAcc set envcode =" + bean.getEnvCode()
                +", host = '"+bean.getHost()+"' ,port = '"+bean.getPort()
                +"' ,username = '"+bean.getUserName()+"' ,password = '"+bean.getPassword()
                + "', path = '" + bean.getPath() + "', scriptpath = '"
                + bean.getScriptPath() + "' where code=" + bean.getCode() + " ";
        /*stat.executeUpdate(sql);
        stat.close();
        conn.close();*/
        DBController.updateData(sql);
        return true;
    }

    public static boolean createEnv(String name) throws Exception{
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();*/
        //for max code value
        String sqlForMaxCode = "select max(code) codemax from LogEXEnv";
        //ResultSet rs = stat.executeQuery(sqlForMaxCode);
        List<Map<String, Object>> rs = DBController.getResult(sqlForMaxCode);
        int code = -1;
        /*while(rs.next()){
            code = rs.getInt(1);
            break;
        }*/
        if(!rs.isEmpty()){
            String codemax = (String)rs.get(0).get("codemax");
            code = (codemax!=null&&!codemax.equals(""))
                    ?Integer.valueOf((String)rs.get(0).get("codemax")):0;
        }
        code++;
        //create new row
        String sql = "INSERT INTO LogEXEnv (name,code) VALUES ('"+name+"',"+code+")";
        int i = DBController.updateData(sql);/*stat.executeUpdate(sql);*/
        /*stat.close();
        conn.close();*/
        if(i==1)
            return true;
        else
            return false;
    }
    public static boolean createAcc(int envCode) throws Exception{
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();*/
        //for max code value
        String sqlForMaxCode = "select max(code) codemax from LogEXAcc";
        //ResultSet rs = stat.executeQuery(sqlForMaxCode);
        List<Map<String, Object>> rs = DBController.getResult(sqlForMaxCode);
        int code = -1;
        /*while(rs.next()){
            code = rs.getInt(1);
            break;
        }*/
        if(!rs.isEmpty()){
            String codemax = (String)rs.get(0).get("codemax");
            code = (codemax!=null&&!codemax.equals(""))
                    ?Integer.valueOf((String)rs.get(0).get("codemax")):0;
        }
        code++;
        EnvironmentBean env = getEnv(String.valueOf(envCode));
        String envRScriptPath = env.getPath();
        String envScriptPath = env.getScriptPath();
        //create new row
        String sql = "INSERT INTO LogEXAcc (code, envcode, path, scriptpath) "
                + "VALUES ("+code+","+envCode+", '"+envRScriptPath+"', '"
                +envScriptPath+"' )";
        int i = DBController.updateData(sql);/*stat.executeUpdate(sql);*/
        /*stat.close();
        conn.close();*/
        if(i==1)
            return true;
        else
            return false;
    }
    public static boolean deleteEnv(EnvironmentBean bean) throws Exception{
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();*/
        String sqlAcc = "DELETE FROM LogEXAcc WHERE envcode = "+bean.getCode();
        String sqlEnv = "DELETE FROM LogEXEnv WHERE code = "+bean.getCode();
        /*stat.executeUpdate(sqlAcc);
        stat.executeUpdate(sqlEnv);
        stat.close();
        conn.close();*/
        DBController.updateData(sqlAcc);
        DBController.updateData(sqlEnv);
        return true;
    }
    public static boolean deleteAcc(AccountBean bean) throws Exception{
        /*Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:config.db");
        Statement stat = conn.createStatement();*/
        String sqlAcc = "DELETE FROM LogEXAcc WHERE code = "+bean.getCode();
        /*stat.executeUpdate(sqlAcc);
        stat.close();
        conn.close();*/
        DBController.updateData(sqlAcc);
        return true;
    }
    
}
